package com.huifei.parseexcel.impl;

import com.huifei.parseexcel.SubExcelService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author: huifei
 * @date: 2017-09-06 13:19
 */
@Component
public class SubExcelServiceImpl implements SubExcelService {


    @Override
    public void parseExcel(String excel, String sumExcel) throws Exception {

        //读取数据
        InputStream is1 = new FileInputStream(excel);
        HSSFWorkbook workbook1 = new HSSFWorkbook(is1);

        HSSFCellStyle style1 = workbook1.createCellStyle();
        style(style1, "1");
        HSSFCellStyle style2 = workbook1.createCellStyle();
        style(style2, "2");

        //得到sheet1数据
        HSSFSheet sheet1 = workbook1.getSheetAt(0);
        System.out.println("总行数：" + sheet1.getLastRowNum());//总行数
        System.out.println("总列数：" + sheet1.getRow(0).getPhysicalNumberOfCells());//总列数

        //读取人事导出数据
        InputStream is2 = new FileInputStream(sumExcel);
        HSSFWorkbook workbook2 = new HSSFWorkbook(is2);
        //得到sheet1数据
        HSSFSheet sheet2 = workbook2.getSheetAt(0);

        List<String> list = new ArrayList<String>();
        List<Integer> index = new ArrayList<Integer>();
        //循环列
        for (int j = 3; j <= sheet1.getRow(0).getPhysicalNumberOfCells(); j++) {

            if (sheet1.getRow(0).getCell(j) != null) {
                int type = sheet1.getRow(0).getCell(j).getCellType();
                if (type == 0) {
                    index.add(j);
                }
                HSSFCell cell = sheet1.getRow(0).getCell(j);
                int num = cell.getCellType();
                SimpleDateFormat sdf = null;
                String str = "";
                if (num == HSSFCell.CELL_TYPE_NUMERIC) {
                    if (cell.getCellStyle().getDataFormat() == 58) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    }
                    double value = cell.getNumericCellValue();
                    Date date = org.apache.poi.ss.usermodel.DateUtil.getJavaDate(value);
                    str = sdf.format(date);
                    list.add(str);
                }
            }
        }

        //循环行
        for (int i = 2; i <= sheet1.getLastRowNum(); i++) {
            HSSFRow row1 = sheet1.getRow(i);
            String name1 = row1.getCell(1).getStringCellValue().trim();

            //循环人事行
            for (int k = 1; k <= sheet2.getLastRowNum(); k++) {
                HSSFRow row2 = sheet2.getRow(k);
                String name2 = row2.getCell(1).getStringCellValue().trim();
                row2.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                String date2 = row2.getCell(2).getStringCellValue();

                String cell3 = "";
                if (row2.getCell(3).getCellType() == 0) {
                    SimpleDateFormat sdf = null;
                    if (row2.getCell(3).getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm:ss")) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    }
                    Date date = row2.getCell(3).getDateCellValue();
                    cell3 = sdf.format(date);
                }

                String cell4 = "";
                if (row2.getCell(4).getCellType() == 0) {
                    SimpleDateFormat sdf = null;
                    if (row2.getCell(4).getCellStyle().getDataFormat() == HSSFDataFormat
                            .getBuiltinFormat("h:mm:ss")) {
                        sdf = new SimpleDateFormat("HH:mm:ss");
                    }
                    Date date = row2.getCell(4).getDateCellValue();
                    cell4 = sdf.format(date);
                }

                int reCell3 = Integer.valueOf(cell3.replace(":", ""));
                int reCell4 = Integer.valueOf(cell4.replace(":", ""));
                for (int a = 0; a < list.size(); a++) {
                    if (name1.equals(name2) && date2.equals(list.get(a))) {

                        int num = index.get(a);
                        row1.createCell(num).setCellValue(cell3);
                        if (reCell3 > 93000) {
                            row1.getCell(num).setCellStyle(style1);
                        }
                        row1.createCell(num + 1).setCellValue(cell4);
                        if (reCell4 < 180000) {
                            row1.getCell(num + 1).setCellStyle(style2);
                        }
                    }

                }

            }

        }
        FileOutputStream fileOut = new FileOutputStream(excel);
        workbook1.write(fileOut);
        is1.close();
        is2.close();
        fileOut.close();
    }

    private static void style(HSSFCellStyle style, String flag) {
        if ("1".equals(flag)) {
            style.setFillForegroundColor(HSSFColor.YELLOW.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        } else {
            style.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);
            style.setFillPattern(CellStyle.SOLID_FOREGROUND);
        }
    }

}
